package MySQLConnection;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;
import java.util.UUID;

import DAO.FormulaDAO;

import model.Criterion;
import model.Formula;

import common.Exceptions.DAOException;
import common.Utils.LinkedSet;

public class MySQLFormulaDAO implements FormulaDAO
{
	private final static String TABLE_NAME = "formula"; 
	private static MySQLFormulaDAO instance;
	
	public static MySQLFormulaDAO getInstance()
	{
		if(instance == null)
			instance = new MySQLFormulaDAO();
		
		return instance;
	}
	
	@Override
	public void insert(Formula item) throws DAOException 
	{
		String query = "INSERT INTO " + MySqlDAOFactory.DBName + "." + TABLE_NAME + "(for_id,for_formula) VALUES('forID', 'forFormula');";

		try 
		{
			query = query.replace("forID", item.getID().toString());
			query = query.replace("forFormula", item.getFormula());
			
			Statement statement = MySqlDAOFactory.getInstance().createStatement();
			statement.executeUpdate(query);
			
			for(Criterion crt : item.getParameters())
			{
				query = "INSERT INTO " + MySqlDAOFactory.DBName + ".forParameters(crt_id, for_id)" + "VALUES('" + crt.getID() + "', '" + item.getID() + "');";
				
				statement = MySqlDAOFactory.getInstance().createStatement();
				statement.executeUpdate(query);
			}
		}
		catch (DAOException e) 
		{
			throw e;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			throw new DAOException("The insert method failed due to some Query error: " + e.getMessage(), e);
		}
		finally
		{
			MySqlDAOFactory.getInstance().closeConnection();
		}
	}

	@Override
	public void update(Formula item) throws DAOException 
	{
		String query = "SELECT * FROM " + TABLE_NAME + " WHERE for_id = '" + item.getID().toString() + "';";

		try 
		{
			Statement statement = MySqlDAOFactory.getInstance().createStatement();
			ResultSet dataSet = statement.executeQuery(query);
			
			if(dataSet.next())
			{
				String updateQuery = "UPDATE " + TABLE_NAME + " set " +
						"for_formula = 'forFormula'" +
						" WHERE for_id = '" + item.getID() + "'";
				
				updateQuery = updateQuery.replace("forFormula", item.getFormula());
				
				statement = MySqlDAOFactory.getInstance().createStatement();
				statement.executeUpdate(updateQuery);
				
				for(Criterion crt : item.getParameters())
				{
					String containsParam = "SELECT * FROM " + MySqlDAOFactory.DBName + ".forParameters WHERE crt_id = 'crtID' and for_id = 'forID'";
					containsParam = containsParam.replace("crtID", crt.getID().toString());
					containsParam = containsParam.replace("forID", item.getID().toString());
					
					statement = MySqlDAOFactory.getInstance().createStatement();
					if(!statement.executeQuery(containsParam).next())
					{
						query = "INSERT INTO " + MySqlDAOFactory.DBName + ".forParameters(crt_id, for_id)" + "VALUES('" + crt.getID() + "', '" + item.getID() + "');";
						
						statement = MySqlDAOFactory.getInstance().createStatement();
						statement.executeUpdate(query);
					}
				}
					
				if(dataSet.next())
					System.err.println("WARING: Two or more " + TABLE_NAME + " with the same ID were found in the database. For_id = " + item.getID());
			}
			else
				throw new DAOException("The " + TABLE_NAME + " identified by the id " + item.getID() + " hasn't been found in the database.");
		}
		catch (DAOException e) 
		{
			throw e;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			throw new DAOException("The insert method failed due to some Query error: " + e.getMessage(), e);
		}
		finally
		{
			MySqlDAOFactory.getInstance().closeConnection();
		}
		
	}

	@Override
	public void delete(UUID id) throws DAOException 
	{
		String query = "SELECT * FROM " + TABLE_NAME + " WHERE for_id = '" + id + "';";

		try 
		{
			Statement statement = MySqlDAOFactory.getInstance().createStatement();
			ResultSet dataSet = statement.executeQuery(query);
			
			if(dataSet.next())
			{
				String deleteQuery = "DELETE FROM " + TABLE_NAME + " WHERE for_id = '" + id + "'";
				
				statement = MySqlDAOFactory.getInstance().createStatement();
				if(statement.executeUpdate(deleteQuery) == 0)
					throw new DAOException("The " + TABLE_NAME + " identified by the id " + id + " couldn't be deleted due to an unknown error.");
					
				if(dataSet.next())
					System.err.println("WARING: Two or more " + TABLE_NAME + " with the same ID were found in the database. for_id = " + id);
			}
			else
				throw new DAOException("The " + TABLE_NAME + " identified by the id " + id + " hasn't been found in the database.");
		}
		catch (DAOException e) 
		{
			throw e;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			throw new DAOException("The delete method failed due to some Query error: " + e.getMessage(), e);
		}
		finally
		{
			MySqlDAOFactory.getInstance().closeConnection();
		}
	}

	@Override
	public List<Formula> getAll() throws DAOException
	{
		String query = "SELECT * FROM " + TABLE_NAME + ";";
		List<Formula> formulas = new LinkedList<Formula>();
		boolean lockOwner = false;

		try 
		{
			Statement statement = MySqlDAOFactory.getInstance().createStatement();
			ResultSet dataSet = statement.executeQuery(query);
			lockOwner = MySqlDAOFactory.lockConnection.tryLock();
			
			while(dataSet.next())
			{
				UUID forID = UUID.fromString(dataSet.getString("for_id"));
				String forFormula = dataSet.getString("for_formula");
				
				String getParametersQuery = "SELECT crt_id FROM forParameters WHERE for_id = '" + forID + "';";
				
				statement = MySqlDAOFactory.getInstance().createStatement();
				ResultSet parametersDataSet = statement.executeQuery(getParametersQuery);
				List<UUID> parametersIDs = new LinkedList<UUID>();
				while(parametersDataSet.next())
					parametersIDs.add(UUID.fromString(parametersDataSet.getString("crt_id")));
				
				if(parametersIDs.size() > 0)
				{
					LinkedSet<Criterion> parameters = new LinkedSet<Criterion>(); 
					parameters.addAll(MySqlDAOFactory.getInstance().getCriterionDAO().getItemsByIDs(parametersIDs));
					Formula formula = new Formula(forID, forFormula, parameters);
					formulas.add(formula);
				}
			}
			
		}
		catch (DAOException e) 
		{
			throw e;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			throw new DAOException("The getAll method failed due to some Query error: " + e.getMessage(), e);
		} 
		finally
		{
			if(lockOwner)
			{
				MySqlDAOFactory.lockConnection.unlock();
				MySqlDAOFactory.getInstance().closeConnection();
			}
		}
		
		return formulas;
	}

	@Override
	public Formula getByID(UUID id) throws DAOException 
	{
		Formula formula = null;
		String query = "SELECT * FROM " + TABLE_NAME + " WHERE for_id = '" + id.toString() + "';";
		boolean lockOwner = false;

		try 
		{
			Statement statement = MySqlDAOFactory.getInstance().createStatement();
			ResultSet dataSet = statement.executeQuery(query);
			lockOwner = MySqlDAOFactory.lockConnection.tryLock();
			
			if(dataSet.next())
			{
				String forFormula = dataSet.getString("for_formula");
				String getParametersQuery = "SELECT crt_id FROM forParameters WHERE for_id = '" + id + "';";
				
				statement = MySqlDAOFactory.getInstance().createStatement();
				ResultSet parametersDataSet = statement.executeQuery(getParametersQuery);
				
				List<UUID> parametersIDs = new LinkedList<UUID>();
				while(parametersDataSet.next())
					parametersIDs.add(UUID.fromString(parametersDataSet.getString("crt_id")));
				
				if(parametersIDs.size() > 0)
				{
					LinkedSet<Criterion> parameters = new LinkedSet<Criterion>(); 
					parameters.addAll(MySqlDAOFactory.getInstance().getCriterionDAO().getItemsByIDs(parametersIDs));
					formula = new Formula(id, forFormula, parameters);
				}
			}
		}
		catch (DAOException e) 
		{
			throw e;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			throw new DAOException("The getByID method failed due to some Query error: " + e.getMessage(), e);
		}
		finally
		{
			if(lockOwner)
			{
				MySqlDAOFactory.lockConnection.unlock();
				MySqlDAOFactory.getInstance().closeConnection();
			}
		}
		
		return formula;
	}

	@Override
	public void deleteParameter(UUID forID, UUID paramID) throws DAOException 
	{
		String query = "DELETE FROM " + MySqlDAOFactory.DBName + ".forParameters WHERE for_id = ? and crt_id = ?";

		try 
		{
			PreparedStatement preStatement = MySqlDAOFactory.getInstance().prepareStatement(query);
			preStatement.setString(1, forID.toString());
			preStatement.setString(2, paramID.toString());
				
			if(preStatement.executeUpdate() == 0)
				throw new DAOException("The formula's parameter identified by the id " + paramID + " couldn't be deleted due to an unknown error.");
					
		}
		catch (DAOException e) 
		{
			throw e;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			throw new DAOException("The delete method failed due to some Query error: " + e.getMessage(), e);
		}
		finally
		{
			MySqlDAOFactory.getInstance().closeConnection();
		}
	}
}
